#!/bin/bash
APP=online_edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi


dws_traffic_user_first_video_play_td="
insert overwrite table ${APP}.dws_traffic_user_first_video_play_td partition (dt = '$do_date')
select user_id,
       min(date_id) play_date_first,
       t1.course_id,
       t3.course_name,
       t3.category_id,
       t3.category_name,
       t3.subject_id,
       t3.subject_name
from ${APP}.dwd_traffic_video_play_inc t1
         left join ${APP}.dim_video_full t2
                   on t1.course_id = t2.course_id
         left join ${APP}.dim_course_subject_category_full t3
                   on t2.course_id = t3.course_id
group by user_id, t1.course_id, t3.course_name, t3.category_id, t3.category_name, t3.subject_id, t3.subject_name;
"

dws_user_user_login_td="
insert overwrite table ${APP}.dws_user_user_login_td partition(dt='$do_date')
select
    u.id,
    nvl(login_date_last,date_format(create_time,'yyyy-MM-dd')),
    nvl(login_count_td,1)
from
(
    select
        id,
        create_time
    from ${APP}.dim_user_zip
    where dt='9999-12-31'
)u
left join
(
    select
        user_id,
        max(dt) login_date_last,
        count(*) login_count_td
    from ${APP}.dwd_user_login_inc
    group by user_id
)l
on u.id=l.user_id;
"

dws_user_new_user_order_td="
insert overwrite table ${APP}.dws_user_new_user_order_td partition(dt)
select
   user_id,
   order_id,
   data_id,
   create_time,
   course_id,
   origin_amount,
   coupon_reduce,
   final_amount,
   data_id
from(
   select
      user_id,
      order_id,
      data_id,
      create_time,
      course_id,
      origin_amount,
      coupon_reduce,
      final_amount,
      row_number() over(partition by user_id order by create_time) rn
   from ${APP}.dwd_trade_order_detail_inc
)t
where t.rn = 1;
"

dws_user_new_user_payment_td="
insert overwrite table ${APP}.dws_user_new_user_payment_td partition(dt)
select
   user_id,
   order_id,
   data_id,
   callback_time,
   alipay_trade_no,
   total_amount,
   trade_body,
   payment_type,
   data_id
from(
   select
      user_id,
      order_id,
      data_id,
      callback_time,
      alipay_trade_no,
      total_amount,
      trade_body,
      payment_type,
      row_number() over(partition by user_id order by callback_time) rn
   from 
   ${APP}.dwd_trade_pay_detail_suc_inc
)t
where rn = 1;
"

case $1 in
      "dws_traffic_user_first_video_play_td" )
         hive -e "$dws_traffic_user_first_video_play_td"
      ;;
      "dws_user_user_login_td" )
         hive -e "$dws_user_user_login_td"
      ;;
      "dws_user_new_user_order_td" )
         hive -e "$dws_user_new_user_order_td"
      ;;
      "dws_user_new_user_payment_td" )
         hive -e "$dws_user_new_user_payment_td"
      ;;
      "all" )
         hive -e "$dws_traffic_user_first_video_play_td$dws_user_user_login_td$dws_user_new_user_order_td$dws_user_new_user_payment_td"
      ;;
esac
